﻿
CREATE PROCEDURE [dbo].[fares_GenereazaJurnalCumparariTVALaIncasare] 
	-- Add the parameters for the stored procedure here
	@CodUnitate smallint, 
	@DeLaData datetime,
	@PanaLaData datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	NrDataDocPlata,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
		CASE WHEN doc.IDTipDocument=10 THEN ValoareTotalaSiTVA ELSE Coalesce(ValPlatCuTVA,0) END  
	ELSE  0  END ValPlatCuTVA, 
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN Baza ELSE Coalesce(BazaExigibila,0) END 
	ELSE  0  END BazaExigibila,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN TVA ELSE Coalesce(TVAExigibil,0) END 
	ELSE  0  END TVAExigibil,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN 0 ELSE doc.TotalValoare - Coalesce(BazaExigibila,0) END 
	ELSE  0  END BazaNeExigibila,
	CASE WHEN doc.IDRegimFiscal IN (4,17,18,19,27) THEN 
	CASE WHEN doc.IDTipDocument=10 THEN 0 ELSE TotalValoareTVA  - Coalesce(TVAExigibil,0) END 
	ELSE  0  END TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
		(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN ValoareTVA ELSE 0 END) TVA,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) InversBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (6) THEN ValoareTVA ELSE 0 END) InversTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (52) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA ELSE 0 END) ImportTaraScutite,
		SUM(CASE WHEN dd.IDRegimFiscal IN (57) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ObligatBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (57) THEN ValoareTVA ELSE 0 END) ObligatTVA,				
		SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) ImportBunuriBaza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN ValoareTVA ELSE 0 END) ImportBunuriTVA,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (55) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCScutit,		
		SUM(CASE WHEN dd.IDRegimFiscal IN (56) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCNeimp,
		--SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) BCBI,
		SUM(CASE WHEN dd.IDRegimFiscal IN (53) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) DRBI,
		SUM(CASE WHEN dd.IDRegimFiscal IN (54) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) NFBI,
		--SUM(CASE WHEN dd.IDRegimFiscal IN (51) THEN ValoareTVA ELSE 0 END) BCTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (53) THEN ValoareTVA ELSE 0 END) DRTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (54) THEN ValoareTVA ELSE 0 END) NFTVA
		
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument) docd 
	ON doc.IDDocument = docd.IDDocument LEFT OUTER JOIN
	(SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
	 SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
	 FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data>=@DeLaData AND dp1.Data<=@PanaLaData
		GROUP BY ddp1.IDDocument) selPlati ON selPlati.IDDocument = docd.IDDocument	
	WHERE 
	tipd.IDTipDocument in (0,10) AND doc.CodUnitate=@CodUnitate AND 
	--LTRIM(RTRIM(part.AtributFiscal))<>'' AND
    (doc.DataDoc>=@DeLaData AND doc.DataDoc<=@PanaLaData)
    
   
    UNION
    --toate cele vechi neplatite
    SELECT NrDoc,DataDoc,doc.CodUnitate,part.CUI,part.Denumire,part.AtributFiscal, 
	selPlati.NrDataDocPlata,
	Coalesce(selPlati.ValPlatCuTVA,0) ValPlatCuTVA, 
	Coalesce(selPlati.BazaExigibila,0) BazaExigibila,
	Coalesce(selPlati.TVAExigibil,0) TVAExigibil,
	doc.TotalValoare - COALESCE(selPlatiAnterior.BazaExigibila,0) -  Coalesce(selPlati.BazaExigibila,0) BazaNeExigibila,
	TotalValoareTVA  - COALESCE(selPlatiAnterior.TVAExigibil,0) - Coalesce(selPlati.TVAExigibil,0) TVANeExigibila,
	docd.*
	FROM 
	Documente doc JOIN Parteneri part ON doc.IDPartener = part.IDPartener
	JOIN TipDocument tipd ON doc.IDTipDocument = tipd.IDTipDocument
	JOIN 
		(SELECT dd.IDDocument,SUM(CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END+ValoareTVA) ValoareTotalaSiTVA,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19)  THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND (dd.CotaTVA=24 OR dd.CotaTVA=19) THEN ValoareTVA ELSE 0 END) TVA24,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) AND dd.CotaTVA=9 THEN ValoareTVA ELSE 0 END) TVA9,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN CASE WHEN IDTipDocument=10 THEN ValoareTotala-ValoareTVA  ELSE ValoareTotala END ELSE 0 END) Baza,
		SUM(CASE WHEN dd.IDRegimFiscal IN (4,17,18,19,27) THEN ValoareTVA ELSE 0 END) TVA,				
		0 InversBaza,0 InversTVA,0 ImportTaraScutite,0 ObligatBaza,0 ObligatTVA,0 ImportBunuriBaza,0 ImportBunuriTVA,0 BCScutit,
		0 BCNeimp,
		--0 BCBI,
		0 DRBI,0 NFBI,
		--0 BCTVA,
		0 DRTVA,0 NFTVA
		FROM DocumenteDetaliu dd JOIN Documente d ON dd.IDDocument=d.IDDocument
		GROUP BY dd.IDDocument) docd 
	ON doc.IDDocument = docd.IDDocument LEFT OUTER JOIN 
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<=@PanaLaData AND dp1.Data>=@DeLaData
		GROUP BY ddp1.IDDocument
	) selPlati ON selPlati.IDDocument = docd.IDDocument LEFT OUTER JOIN 	
	(
		SELECT MAX(dp1.Numar +'/' +CONVERT(varchar(20),dp1.Data,104)) NrDataDocPlata,SUM(Valoare) ValPlatCuTVA,
		SUM(Valoare - ValoareTVA) BazaExigibila,SUM(ddp1.ValoareTVA) TVAExigibil, ddp1.IDDocument 
		FROM DocumentePlataDetaliu ddp1 JOIN DocumentePlata dp1 ON dp1.IDDocumentPlata=ddp1.IDDocumentPlata
		WHERE dp1.Data<@DeLaData 
		GROUP BY ddp1.IDDocument
	) selPlatiAnterior ON selPlatiAnterior.IDDocument = docd.IDDocument
	WHERE 
	  YEAR(doc.DataDoc) > 2012 AND                     -- dupa 2012 
	  tipd.IDTipDocument in (0) AND                 -- pentru facturile de cumparare fara bonuri fiscale
	  doc.CodUnitate=@CodUnitate AND                -- pentru unitatea curenta
	  --LTRIM(RTRIM(part.AtributFiscal))<>'' AND      -- pentru partenerii care au atribut fiscal
	  doc.DataDoc < @DeLaData AND                   -- pentru facturile de dinaintea intervalului selectat
	  --doc.DataDoc >= DATEADD(day,-90,@DeLaData) AND -- dar nu mai vechi de 90 de zile
	  doc.IDRegimFiscal IN (4,17,18,19,27) AND      -- cu regim fiscal cu TVA interne
	  --ValoareTotalaSiTVA >= COALESCE(ValPlatCuTVA,0) AND
      EXISTS(SELECT * FROM DocumenteDetaliu dd WHERE dd.IDDocument = docd.IDDocument AND dd.TVAIncasare=1) AND 
                                                    -- pentru facturile care au o linie cu tVA la incasare
      ABS(doc.Total) >  ABS(COALESCE((SELECT SUM(dpd.Valoare) FROM DocumentePlataDetaliu dpd JOIN DocumentePlata dp ON dpd.IDDocumentPlata = dp.IDDocumentPlata
	  WHERE dpd.IDDocument = docd.IDDocument AND dp.Data < @DeLaData),0))
												    -- care au ramas neachitate pana in perioada selectata 
    
	ORDER BY DataDoc
		
END